2015-12-16 - 23113 - Spec - E-Accounting for Mexico #FICOSupport #Mexico #GLAccount #FIReports

SPECIFICATIONS


23113 - Spec - E-Accounting for Mexico


Purpose

Be compliant with Mexico Fiscal requirements for the accounting process

Admin Info

Purpose
 Be compliant with Mexico Fiscal requirements for the accounting process
Requested By
 Baiwa/Jingsha
Spec Created By
 Raghav
Spec Created Date
 12/17/2015
Spec QA by
 Raghav/Karthik
Objects
 ZSATCODES
ZSATCODE
TVDIR
TDDAT
ZNATURE
ZSAT
ZSATACCT_CAT
ZSATTRIAL_BAL
ZSHP
ZSATCODES S
TVARVC
ZSAT_ACCT_CAT
ZSAT_TRL_BAL
ZSAT_CODES
ZHELP
ZSATPOLICY_PER
ZSAT_POP
TVARVC
Document Status
 Completed


Estimates


Sl.No
 Activity
 Estimation in Hours
1
 Research
 40
2
 Documentation
 15
3
 Development/ Config
 60
4
 Unit test in DEV
 50
5
 Unit test in QUA
 30
6
 Other activity
 10

TOTAL
 205

References

Prior Tickets

[Provide links of prior associated Spec / Break Fix BOSS document(s)]



Documents

[Attach any document(s) received for the requirement(s)]



Spec Changes

[List the changes made to program after the approval of the original requirement along with the Date on which the change request was received and the name of the initiator]

Sl.
 Change Details
 Requested By
 Requested On
 Notes if any
1
 Add the new field of line item count at header for accounts catalog file
 Baiwa
 01/08/2016
 Asked to remove the same on 01/14/2016
2
 Line items field should be separated by special character 'I' in accounts catalog
 Baiwa
 01/08/2016
3
 In trial balance file negative symbol '-' should appear on left side of the value
 Baiwa
 01/26/2016
4
 Date at header is not required in trial balance file
 Baiwa
 01/26/2016
5
 File should saved through FTP
 Baiwa
 02/11/2016
6
 File naming name for Accounts catalog and Trial Balance
 Baiwa
 02/16/2016
7
 Create 'Z' Transaction for Programs and Table
 Baiwa
 02/29/2016
8
 Add period and year for the accounts catalog screen variant
 Baiwa
 03/09/2016
9
 Revised opening balance calculation logic
 Baiwa
 03/10/2016
10
 Keep an archive of all the files in AL11 which we sent to EDICOM
 Baiwa
 03/15/2016
11
 Field CONCEPTO in DTRANS should be from Header text field and UUID_CFDI in DCOMPNAL should be from reference field from SAP
 Baiwa
 09/15/2016
12
 File DCOMPNAL or DCOMPEXT should come under the DTRANS based on document number
 Baiwa
 09/28/2016
13
 File DCOMPNAL and DCOMPEXT should display all the the line item data and with respect to the amount.
 Baiwa
 10/04/2016

Functional Requirement

Purpose/WHY:

[Explain the purpose of the project and the reason why this requirement has come]

Mexico accounting file extraction for reporting.
  1. Accounts Catalog (Chart of account)
  2. Trial Balance
  3. Policy of Period

Create the 'Z' transactios for for programs and table
  1. Accounts Catalog
  2. Trial Balance
  3. Policy of Period
and for Table ZSATCODES

Electronic Accounting proposal v1_EN .doceaccounting specs-1 Ans.xlsx

In Scope:

[List the activities to be included in scope]
Mexico E-Accounting Q&A.doc

Out of Scope:

[Out of scope activities]

Solution Summary

[Discuss this section with Requester and get approval prior to beginning work]

Mexico E-Accounting process
For the extraction file as per the Mexico format we have to create a custom table as below
Company code
 Level
 Grouping code
 SAP GL account
 Name of account or sub-account
 Version
 Nature of account















A) Accounts Catalogue- As the Business requirement mexico wants to display their own gl account no. and description in Spain language in the reporting file, we have to create a new 'Z' table to store the GL account info also to write a new 'Z' program to generate the Accounts Catalogue file.

Selection screen input fields:-

Accounts catalog (COA) file should be picked based on the input period and year which should match with the ZSATCODES table less than or equal to period
ACCOUNTS CATALOG
Header
Field
 Type
 Length
 Status
 Output file fields
 Description
ID/LABEL
 X
 15
 (M)
 Hard code CABCAT
VERSION
 X
 3
 (M)
 Hard code 1.1
Tax ID
 X
 13
 (M)
 T001Z-PAVAL
 OBY6 RFC field
TOTALCTAS
 N
 10
 (M)
 No. of line items
MES
 X
 2
 (M)
 System Month
 Default input Month
ANO
 N
 4
 (M)
 System Year
 Default input Year
Item
ID/LABEL
 X
 15
 (M)
 Hard code LINCTA
CODAGRUP
 X
 6
 (M)
 ZSATCODES-CODAGRUP
 SAT Grouping Code
NUMCTA
 X
 100
 (M)
 ZSATCODES-NUMCTA
 SAP GL Account
DESC
 X
 400
 (M)
 ZSATCODES-ZDESC
 Name of account or sub-account
SUBCTADE
 X
 100
 (O)
 ZSATCODES-CODAGRUP
 In case of sub account main account grouping code no. else blank
NIVEL
 N
 5
 (M)
 ZSATCODES-NIVEL
 Level of the Grouping Code
NATUR
 X
 1
 (M)
 ZSATCODES-NATUR
 Nature of Account

B) Trial Balance- As per the requirements all the accounts in the trial balance file should display opening, debit, credit and closing balance values. With this program it should pick the common GL accounts from custom table and SAP SKB1 table then pick the values from FAGLFLEXT with the input period and year.
Calculation of GL value

Selection Screen input fields:-

Opening Balance- Exclude the input period and year (for example:- the input period is 12 and year 2015 then the calculation will be <period 12 and year 2015 in table FAGLFLEXT then calculate the debit and credit balance of all Gl accounts to get the opening balance)

TRIAL BALANCE
Header
Field
 Type
 Length
 Status
 Output file fields
 Description
ID/LABEL
 X
 15
 (M)
 Hard code CABCAT
VERSION
 X
 3
 (M)
 Hard code 1.1
Tax ID
 X
 13
 (M)
 T001Z-PAVAL
 OBY6 RFC field
MES
 X
 2
 (M)
 MONAT
 Screen Input Month
ANO
 N
 4
 (M)
 GJAHR
 Screen Input year
TIPOENVIO
 X
 15
 (M)
 Screen Field (Character Default 'N')
 Character can be changed to 'C' when required
FECHAMODBAL
 X
 8
 (C)
 Default program run date DDMMYYYY
Item
ID/LABEL
 X
 15
 (M)
 Hard code LINCTA
NUMCTA
 X
 100
 (M)
 ZSATCODES-NUMCTA
 SAP GL Account
SALDOINI
 X
 20,2
 (M)
 (1) FAGLFLEXT-HSLVT + FAGLFLEXT-HSL(for previous period) (calculated based on Debit and Credit values less than the input period)
 Opening balance of Gl Account
DEBE
 X
 20,2
 (M)
 (2) FAGLFLEXT-HSL(for input period) (calculated based on Debit value with the given input period and year)
 Debit balance of Gl account for given month
HABER
 N
 20,2
 (M)
 (3) FAGLFLEXT-HSL(for input period) (calculated based on Credit value with the given input period and year)
 Credit balance of Gl account for given month
SALDOFIN
 N
 20,2
 (M)
 (1)+(2)-(3)
 Total sum



C) Policy of Period:-

Selection Screen input fields:-
Policy of Period
Header
Field
 Type
 Length
 Status
 Output file fields
 Description
ID/ETIQUETA
 X
 15
 (M)
 Hardcode: CABPLZ
VERSION
 X
 3
 (M)
 Entered in the variant/selection criteria: default 1.1
Tax ID
 X
 13
 (M)
 T001Z –PAVAL
Based on company code, and Party = MX_RFC
 OBY6 RFC field
MES
 X
 2
 (M)
 MONAT
 Screen Input Month
ANIO
 N
 4
 (M)
 GJAHR
 Screen Input year
TIPOSOLICITUD
 X
 2
 (M)
 Entered in the variant/selection criteria
NUMORDEN
 X
 13
 (C)
 Entered in the variant/selection criteria (provided by the government when they request the file)
NUMTRAMITE
 X
 10
 (C)
 Entered in the variant/selection criteria (provided by the government when they request the file)
Header
ID/ETIQUETA
 X
 15
 (M)
 hardcode: DPLZ
IDPLZ
 X
 15
 (M)
 COCDYYYYMMDDHHMMSS
 Company code + timestamp
NUMUNIDENPOL
 X
 50
 (M)

FECHA
 X
 8
 (M)
 System date DDMMYYYY
 Policy file generation date
CONCEPTO
 X
 300
 (M)

Header
ID/ETIQUETA
 X
 15
 (M)
 hardcode: DTRANS
IDPLZ
 X
 15
 (M)
 COCDYYYYMMDDHHMMSS (from file IDPLZ)
 Company code + timestamp
IDTRANS
 X
 15
 (M)
 BKPF-BELNR
NUMCTA
 X
 100
 (M)
 BKPF-BELNR= BSEG-BELNR then BSEG-HKONT = ZSATCODES-CODAGRUP
 Grouping code from account catalogue (based on BSEG GL account number, convert into the mapped SAT Account code in z table )
DESCTA
 X
 100
 (M)
 ZSATCODES-ZDESC
 Account description from Z table
CONCEPTO
 X
 200
 (M)
 BSEG-SGTXT
 Transaction description from item long text
DEBE
 N
 20,2
 (M)
 IF BSEG-SHKZG is S then BSEG-WRBTR
 Debit amount
HABER
 N
 20,2
 (M)
 IF BSEG-SHKZG is H then BSEG-WRBTR
 Credit amount

Policy of Period
Header
Field
 Type
 Length
 Status
 Output file fields
 Description
ID/ETIQUETA
 X
 15
 (M)
 Hardcode: CABPLZ
VERSION
 X
 3
 (M)
 Entered in the variant/selection criteria: default 1.1
Tax ID
 X
 13
 (M)
 T001Z –PAVAL
Based on company code, and Party = MX_RFC
 OBY6 RFC field
MES
 X
 2
 (M)
 MONAT
 Screen Input Month
ANIO
 N
 4
 (M)
 GJAHR
 Screen Input year
TIPOSOLICITUD
 X
 2
 (M)
 Entered in the variant/selection criteria
NUMORDEN
 X
 13
 (C)
 Entered in the variant/selection criteria (provided by the government when they request the file)
NUMTRAMITE
 X
 10
 (C)
 Entered in the variant/selection criteria (provided by the government when they request the file)
Header
ID/ETIQUETA
 X
 15
 (M)
 hardcode: DPLZ
IDPLZ
 X
 15
 (M)
 COCDYYYYMMDDHHMMSS
(company code + timestamp)
NUMUNIDENPOL
 X
 50
 (M)

FECHA
 X
 8
 (M)
 System date DDMMYYYY
 Policy file generation date
CONCEPTO
 X
 300
 (M)

Header
ID/ETIQUETA
 X
 15
 (M)
 hardcode: DTRANS
IDPLZ
 X
 15
 (M)
 COCDYYYYMMDDHHMMSS (from file IDPLZ)
(company code + timestamp)
IDTRANS
 X
 15
 (M)
 DocType+DocNumber
 BKPF-BLART+BKPF-BELNR
NUMCTA
 X
 100
 (M)
 BKPF-BELNR= BSEG-BELNR then BSEG-HKONT = ZSATCODES-CODAGRUP
 Grouping code from account catalogue (based on BSEG GL account number, convert into the mapped SAT Account code in z table )
DESCTA
 X
 100
 (M)
 ZSATCODES-ZDESC
 Account description from Z table
CONCEPTO
 X
 200
 (M)
 BSEG-SGTXT
 Transaction description from item long text
DEBE
 N
 20,2
 (M)
 IF BSEG-SHKZG is S then BSEG-WRBTR
 Debit amount
HABER
 N
 20,2
 (M)
 IF BSEG-SHKZG is H then BSEG-WRBTR
 Credit amount
Item
ID/ETIQUETA
 X
 15
 (M)
 hardcode: DCOMPNAL
IDPLZ
 X
 15
 (M)
 COCDYYYYMMDDHHMMSS (from file IDPLZ)
(company code + timestamp)
IDTRANS
 X
 15
 (M)
 DocType+DocNumber
 BKPF-BLART+BKPF-BELNR
IDCOMPNAL
 X
 15
 (M)
 Reference field
BKPF-XBLNR
UUID_CFDI
 X
 36
 (M)
 Reference field / header text
BKPF-BKTXT
 Decision: Enter UUID after invoices are created and saved
Tax ID
 X
 13
 (M)
 T001Z –PAVAL
Based on company code, and Party = MX_RFC
MONTOTOTAL
 N
 20,2
 (M)
 Total amount for the document
 BSEG-WRBTR
MONEDA
 X
 3
 (C)
 Currency
 If BSEG-WAERS is MXN
TIPCAMB
 N
 19,5
 (C)
 Exchange rate
 BKPF-KURSF
Item
ID/ETIQUETA
 X
 15
 (M)
 hardcode: DCOMPEXT
IDPLZ
 X
 15
 (M)
 COCDYYYYMMDDHHMMSS (from file IDPLZ)
(company code + timestamp)
IDTRANS
 X
 15
 (M)
 DocType+DocNumber
 BKPF-BLART+BKPF-BELNR
IDCOMPEXT
 X
 15
 (M)
 Reference field
BKPF-XBLNR
NUMFACTEXT
 X
 36
 (M)
 Reference field / header text
BKPF-BKTXT
TAXID
 X
 30
 (C)
 T001Z –PAVAL
Based on company code, and Party = MX_RFC
MONTOTOTAL
 N
 20,2
 (M)
 Amount
 BSEG-WRBTR
MONEDA
 X
 3
 (C)
 Currency
 If BSEG-WAERS is other than MXN
TIPCAMB
 N
 19,5
 (C)
 Exchange rate
 BKPF-KURSF

5) FTP and archive files names, path and server details. T.code STVARV
Name
 Path
 Case-sensitive
ZIFI_SAT_EDICOM_AC_PATH
 /out/CATALOGOCUENTAS_LOT_NEC120213LFA/EDIWIN
 X
ZIFI_SAT_EDICOM_POP_PATH
 /out/PLZ_POLIZAS_1_1_LOT_NEC120213LFA/EDIWIN
 X
ZIFI_SAT_EDICOM_ARCHV
 /usr/sap/NED/EDI/EDICOM/EACCOUNTING/ARCHIVE
 X
ZIFI_SAT_EDICOM_PORT
 2021
ZIFI_SAT_EDICOM_PWD
 fffkbknbu
 X
ZIFI_SAT_EDICOM_SERVER
 aspebi.sedeb2b.com
 X
ZIFI_SAT_EDICOM_TB_PATH
 /out/BCE_BALANZA_LOT_NEC120213LFA/EDIWIN
 X
ZIFI_SAT_EDICOM_USER
 NEC120213LFA_T
6) File naming name for Accounts catalog and Trial Balance
Name of the files
Accounts Catalog - COACocd_YYYYMMDD_HHMMSS
Trial Balance - TBCocd_YYYYMMDD_HHMMSS
Policy of Period - POPCocd_YYYYMMDD_HHMMSS

7) New 'Z' transaction codes should be
S.no
 Description
 Program/Table
 New 'Z' transaction
1
 SAT Custom Table
 ZSATCODES
 ZSAT_CODES
2
 Accounts Catalog
 ZSATACCT_CAT
 ZSAT_ACCT_CAT
3
 Trial Balance
 ZSATTRIAL_BAL
 ZSAT_TRL_BAL
4
 Policy of period
 ZSATPOLICY_PER
 ZSAT_POP

Test Plan

[List test scenarios/cases to be executed here]

1) COA and Trial balance file extraction
Test ID
 Test Scenario
 Expected Results
FI-001
 File extraction for accounts catalog with the SAT table and SAP GL account
 Program should read the data from SAT table and SAP gl accounts and should only post the common data maintained.
FI-002
 File extraction for trial balance of GL accounts with period 12
 File should display the all opening balance, debit, credit and closing balance value
FI-003
 File extraction for trial balance of GL accounts with period 13
 File should display the all opening balance, debit, credit and closing balance value of the period 12

Test ID
 Test Scenario
 Expected Results
FI-001
 COAs catalog with the SAT table for company code 0028
 Program should read the data form ZSATCODES table with the SAP Gl account and generate the output with values also should exclude the unmached data.
FI-002
 Trial Balance file with the SAT table for company code 0028
 Program should read the data form ZSATCODES table with the SAP Gl account and generate the output with values also should exclude the unmached data.
2) File generation and sending it to the FTP server
Test ID
 Test Scenario
 Expected Results
FI-001
 File transfer through FTP with given file name and file content should be as per the input for company code 0028
 Both program COA and TB should read the data from table as per the given input and file should save in the secured FTP location.
FI-002
 File transfer through FTP with given file name and file content should be as per the input for company code 0029
 Both program COA and TB should read the data from table as per the given input and file should save in the secured FTP location.
3) File generation with multiple company codes with new Z t.codes
Test ID
 Test Scenario
 Expected Results
FI-001
 New 'Z' Transaction for program Accounts catalog with muliple company code 0028 & 0029
 New 'Z' code ZSAT_ACCT_CAT for accounts catalog program should run with multiple company codes for the data provided with custom table new code ZSAT_CODES
FI-002
 New 'Z' Transaction for program trial Balance with muliple company code 0028 & 0029
 New 'Z' code ZSAT_TRL_BAL for trial balance program should run with multiple company codes for the data provided with custom table new code ZSAT_CODES
4) Changes received while testing in NEQ
S.No
 Change
 Description
 Result
1
 Duplicate Gl
 Same GL accounts should not repeat for same company code
 Fixed
2
 Opening balance
 Pervious year opening balance should be calculated for only balance sheet Gl accounts
 Fixed
3
 Screen Field
 COA display screen should have input fields period and year so header will represent the same
 Fixed

Test ID
 Test Scenario
 Expected Results
FI-001
 Accounts catalog file for company code 0028 and 0029 with screen selection period and year
 Accounts catalog file should run with the given input period and year at the header
FI-002
 Trial balance file for company code 0028 and 0029
 Trial balance file should calculate and display opening, debit, credit and closing balance
FI-003
 Trial balance file for company code 0028 and 0029 with period 13
 Trial balance file should pick the values of period 12 and display the period 13 at the header

Solution Details

[Provide complete technical details for configuration or programming here]

1)The details of FTP server credentials Host Server,Port,Username and password ,Archive path are maintained in TVARVC table.
2)In ZSATACCT_CAT program,we are getting the data from ZSATCODES table for the given company code and G/L Account.
3)In ZSATTRIAL_BAL program, the inputs are company code,G/L Account,Character(C/N),Period and Fiscal year.
4)To get the data finally in ZSATTRIAL_BAL program ,FAGLFLEXT table is taken as reference and the entries are kept in a file.
5)Then the file is sent to FTP server and kept the copy of file in Archive of specified file path in AL11.
6)In ZSATPOLICY_PER program we added logic to get DTRANS,DCOMPNAL and DCOMPEXT sections for the given inputs of period,year,tiposoli and company code.
7)Added logic to send a file to FTP server as per the requirement so that DCOMPNAL, DCOMPEXT sections is displayed after DTRANS for each document number(BELNR) .
8)For Mexican currency we are displaying DCOMPNAL section and for other we are displaying DCOMPEXT section of fieds.
9)Created ZSAT_POP as Tcode for ZSATPOLICY_PER program to give inputs at this transaction send file to FTP server.
10)Added logic in the program to get credentials of FTP server from TVARVC table.

Issues

[List Issues / Bugs identified in configuration or development]

1) Trial Balance opening balance value is incorrectly calculated
2) Custom table ZSATCODES have to check the duplicate entry with company code and Gl account.
3) Period length for trial balance input field is showing 3 character field but it should allow only 2.


S.no
 Description
 Program/Table
 New 'Z' transaction
1
SAT Custom Table
 ZSATCODES
 ZSAT_CODES
2
Accounts Catalog
 ZSATACCT_CAT
 ZSAT_ACCT_CAT
3
Trial Balance
 ZSATTRIAL_BAL
 ZSAT_TRL_BAL